package com.example.rabc.dao.impl;

import com.example.rabc.dao.UserDao;
import com.example.rabc.entity.User;
import com.example.rabc.config.DatabaseConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * MySQL用户DAO实现类
 */
public class MySqlUserDao implements UserDao {
    
    private final DatabaseConnectionManager connectionManager;
    
    public MySqlUserDao() {
        this.connectionManager = DatabaseConnectionManager.getInstance();
    }

    /**
     * 保存用户到数据库
     * @param entity 实体对象 包含用户信息
     * @return 保存后的用户实体对象
     */
    @Override
    public User save(User entity) {
        String sql = "INSERT INTO users (username, password, email, real_name, status, created_time, updated_time) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            stmt.setString(1, entity.getUsername());
            stmt.setString(2, entity.getPassword());
            stmt.setString(3, entity.getEmail());
            stmt.setString(4, entity.getRealName());
            stmt.setInt(5, entity.getStatus());
            stmt.setTimestamp(6, Timestamp.valueOf(entity.getCreatedTime()));
            stmt.setTimestamp(7, Timestamp.valueOf(entity.getUpdatedTime()));
            
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        entity.setId(rs.getLong(1));
                    }
                }
            }
            return entity;
        } catch (SQLException e) {
            throw new RuntimeException("保存用户失败", e);
        }
    }

    /**
     * 更新数据库中的用户信息
     * @param id 实体ID
     * @param entity 实体对象
     * @return 更新后的用户实体对象
     */
    @Override
    public User update(Long id, User entity) {
        String sql = "UPDATE users SET username=?, password=?, email=?, real_name=?, status=?, updated_time=? WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, entity.getUsername());
            stmt.setString(2, entity.getPassword());
            stmt.setString(3, entity.getEmail());
            stmt.setString(4, entity.getRealName());
            stmt.setInt(5, entity.getStatus());
            stmt.setTimestamp(6, Timestamp.valueOf(entity.getUpdatedTime()));
            stmt.setLong(7, id);
            
            stmt.executeUpdate();
            entity.setId(id);
            return entity;
        } catch (SQLException e) {
            throw new RuntimeException("更新用户失败", e);
        }
    }

    /**
     * 根据ID删除数据库中的用户
     * @param id 实体ID
     */
    @Override
    public void deleteById(Long id) {
        String sql = "DELETE FROM users WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setLong(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("删除用户失败", e);
        }
    }

    /**
     * 根据ID查询数据库中的用户
     * @param id 实体ID
     * @return 用户实体对象的Optional包装
     */
    @Override
    public Optional<User> findById(Long id) {
        String sql = "SELECT * FROM users WHERE id=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setLong(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询用户失败", e);
        }
        return Optional.empty();
    }

    /**
     * 查询数据库中的所有用户
     * @return 用户实体对象列表
     */
    @Override
    public List<User> findAll() {
        String sql = "SELECT * FROM users";
        List<User> users = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                users.add(mapRowToEntity(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询所有用户失败", e);
        }
        return users;
    }

    /**
     * 分页查询数据库中的所有用户
     * @param page 页码（从0开始）
     * @param size 每页大小
     * @return 分页后的用户实体对象列表
     */
    @Override
    public List<User> findAll(int page, int size) {
        String sql = "SELECT * FROM users LIMIT ? OFFSET ?";
        List<User> users = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, size);
            stmt.setInt(2, page * size);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("分页查询用户失败", e);
        }
        return users;
    }

    /**
     * 统计数据库中的用户数量
     * @return 用户数量
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM users";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException("统计用户数量失败", e);
        }
        return 0;
    }

    /**
     * 根据用户名查询数据库中的用户
     * @param username 用户名
     * @return 用户实体对象的Optional包装
     */
    @Override
    public Optional<User> findByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, username);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据用户名查询用户失败", e);
        }
        return Optional.empty();
    }

    /**
     * 根据邮箱查询数据库中的用户
     * @param email 邮箱
     * @return 用户实体对象的Optional包装
     */
    @Override
    public Optional<User> findByEmail(String email) {
        String sql = "SELECT * FROM users WHERE email=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, email);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据邮箱查询用户失败", e);
        }
        return Optional.empty();
    }

    /**
     * 根据状态查询数据库中的用户列表
     * @param status 状态
     * @return 用户实体对象列表
     */
    @Override
    public List<User> findByStatus(Integer status) {
        String sql = "SELECT * FROM users WHERE status=?";
        List<User> users = new ArrayList<>();
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, status);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapRowToEntity(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据状态查询用户失败", e);
        }
        return users;
    }

    /**
     * 检查数据库中是否存在指定用户名的用户
     * @param username 用户名
     * @return 是否存在
     */
    @Override
    public boolean existsByUsername(String username) {
        String sql = "SELECT COUNT(*) FROM users WHERE username=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, username);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("检查用户名是否存在失败", e);
        }
        return false;
    }

    /**
     * 检查数据库中是否存在指定邮箱的用户
     * @param email 邮箱
     * @return 是否存在
     */
    @Override
    public boolean existsByEmail(String email) {
        String sql = "SELECT COUNT(*) FROM users WHERE email=?";
        try (Connection conn = connectionManager.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, email);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("检查邮箱是否存在失败", e);
        }
        return false;
    }
    
    /**
     * 将ResultSet行映射为User实体
     * @param rs ResultSet
     * @return User实体
     * @throws SQLException SQL异常
     */
    private User mapRowToEntity(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setEmail(rs.getString("email"));
        user.setRealName(rs.getString("real_name"));
        user.setStatus(rs.getInt("status"));
        user.setCreatedTime(rs.getTimestamp("created_time").toLocalDateTime());
        user.setUpdatedTime(rs.getTimestamp("updated_time").toLocalDateTime());
        return user;
    }
}